package de.tum.getraenke;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DrinkDBHandler extends SQLiteOpenHelper {
	// the update time for a drink
    public final static int DRINK_UPDATE_TIME = 86400; // every day we want to update the drinks
    
	final private static String TAG = DrinkDBHandler.class.getSimpleName();
	// name and version of the database
	final private static String DATABASE_NAME = "android_drinks.db";
	final private static int DATABASE_VERSION = 1;
	// names and attributes of the drinks table
	final public static String TABLE_DRINKS = "drinks";
	final public static String DRINK_ID = "_id";
	final public static String DRINK_GTIN = "gtin";
	final public static String DRINK_NAME = "name";
	final public static String DRINK_PACKING = "packing";
	final public static String DRINK_SIZE = "size";
	final public static String DRINK_PRICE = "price";
	final public static String DRINK_DEPOSIT = "deposit";
	final public static String DRINK_IMAGE = "image";
	final public static String DRINK_IS_FAVORITE = "isFavorite";
	final public static String DRINK_LAST_UPDATE = "lastUpdate";
	// names and attributes of the order table
	final public static String TABLE_ORDER = "orders";
	final public static String ORDER_ID = "_id";
	final public static String ORDER_DRINK_ID = "drinkId";
	final public static String ORDER_AMOUNT = "amount";
	final public static String ORDER_LAST_TRY = "lastTryTime";
	final public static String ORDER_IS_BOOKED = "isBooked";
	
	public DrinkDBHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE "
				+ TABLE_DRINKS + " (" + DRINK_ID 
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ DRINK_GTIN + " VARCHAR(250), "
				+ DRINK_NAME + " VARCHAR(250), "
				+ DRINK_PACKING + " VARCHAR(250), "
				+ DRINK_IMAGE + " VARCHAR(250), "
				+ DRINK_SIZE + " FLOAT, "
				+ DRINK_PRICE + " FLOAT, "
				+ DRINK_DEPOSIT + " FLOAT, "
				+ DRINK_IS_FAVORITE + " BOOLEAN, "
				+ DRINK_LAST_UPDATE + " INTEGER);");
		db.execSQL("CREATE TABLE "
				+ TABLE_ORDER + " (" + ORDER_ID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ ORDER_DRINK_ID + " INTEGER, "
				+ ORDER_AMOUNT + " INTEGER, "
				+ ORDER_LAST_TRY + " INTEGER,"
				+ ORDER_IS_BOOKED + " BOOLEAN);");
		if(true) {
			int a = 1;
			int b = 2;
			int c = 3;
			Log.d(TAG, String.valueOf(a) + String.valueOf(b) + String.valueOf(c));
		}
	}
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_DRINKS);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ORDER);
		onCreate(db);
	}
	
	@SuppressWarnings("finally")
	public long insertDrink(Drink drink) {
		long rowId = -1;
		try {
			SQLiteDatabase db = getWritableDatabase();
			ContentValues values = new ContentValues();
			values.put(DRINK_GTIN, drink.getGtin());
			values.put(DRINK_NAME, drink.getName());
			values.put(DRINK_PACKING, drink.getPacking());
			values.put(DRINK_IMAGE, drink.getImage());
			values.put(DRINK_SIZE, drink.getSize());
			values.put(DRINK_PRICE, drink.getPrice());
			values.put(DRINK_DEPOSIT, drink.getDeposit());
			values.put(DRINK_IS_FAVORITE, false);
			values.put(DRINK_LAST_UPDATE, System.currentTimeMillis());
			rowId = db.insert(TABLE_DRINKS, null, values);
		} catch (SQLiteException e) {
			e.printStackTrace();
		} finally {
			return rowId;
		}
	}
	
	@SuppressWarnings("finally")
	public long insertOrder(Drink drink, int amount) {
		long rowId = -1;
		try {
			SQLiteDatabase db = getReadableDatabase();
			ContentValues values = new ContentValues();
			values.put(ORDER_DRINK_ID, String.valueOf(getDrinkIdByGtin(drink.getGtin())));
			values.put(ORDER_LAST_TRY, System.currentTimeMillis());
			values.put(ORDER_AMOUNT, amount);
			values.put(ORDER_IS_BOOKED, false);
			rowId = db.insert(TABLE_ORDER, null, values);
		} catch (SQLiteException e) {
			e.printStackTrace();
		} finally {
			return rowId;
		}
	}
	
	public int updateDrinkIfNecessary(Drink drink) {
		SQLiteDatabase db = getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(DRINK_GTIN, drink.getGtin());
		values.put(DRINK_NAME, drink.getName());
		values.put(DRINK_PACKING, drink.getPacking());
		values.put(DRINK_IMAGE, drink.getImage());
		values.put(DRINK_SIZE, drink.getSize());
		values.put(DRINK_PRICE, drink.getPrice());
		values.put(DRINK_DEPOSIT, drink.getDeposit());
		values.put(DRINK_LAST_UPDATE, System.currentTimeMillis());
		int drinkId = getDrinkIdByGtin(drink.getGtin());
		long lastUpdateLimit = System.currentTimeMillis() - DRINK_UPDATE_TIME;
		return db.update(TABLE_DRINKS, values, DRINK_ID + " = ? AND " + DRINK_LAST_UPDATE + " < ?",
				new String[] { String.valueOf(drinkId), String.valueOf(lastUpdateLimit) });
	}

	public int getDrinkIdByGtin(String gtin) {
		SQLiteDatabase db = getReadableDatabase();
		Cursor c = db.query(TABLE_DRINKS, new String[] { DRINK_ID }, DRINK_GTIN + " = ?", new String[] { gtin }, null, null, null);
		int result;
		if(c.moveToFirst()) {
			 result = c.getInt(0);
		} else {
			result = -1;
		}
		c.close();
		return result;
	}
	
	public int setFavorite(Drink drink, boolean favorite) {
		SQLiteDatabase db = getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(DRINK_IS_FAVORITE, favorite);
		return db.update(TABLE_DRINKS, values, DRINK_ID + " = ?",
				new String[] { String.valueOf(getDrinkIdByGtin(drink.getGtin())) });
	}
	
	public Cursor getFavorites() {
		SQLiteDatabase db = getReadableDatabase();
		return db.query(TABLE_DRINKS, null, DRINK_IS_FAVORITE + " = ?", new String[] { "1" }, null, null, DRINK_NAME);
	}
	
	public Drink getDrink(int drinkId) {
		SQLiteDatabase db = getReadableDatabase();
		Cursor c = db.query(TABLE_DRINKS, null, DRINK_ID + " = ?", new String[] { String.valueOf(drinkId) }, null, null, null);
		if(c.moveToFirst()) {
			Drink drink = new Drink();
			drink.setGtin(c.getString(c.getColumnIndex(DRINK_GTIN)));
			drink.setName(c.getString(c.getColumnIndex(DRINK_NAME)));
			drink.setPacking(c.getString(c.getColumnIndex(DRINK_PACKING)));
			drink.setImage(c.getString(c.getColumnIndex(DRINK_IMAGE)));
			drink.setSize(c.getDouble(c.getColumnIndex(DRINK_SIZE)));
			drink.setPrice(c.getDouble(c.getColumnIndex(DRINK_PRICE)));
			drink.setDeposit(c.getDouble(c.getColumnIndex(DRINK_DEPOSIT)));
			drink.setLastUpdate(c.getInt(c.getColumnIndex(DRINK_LAST_UPDATE)));
			return drink;			
		}
		c.close();
		return null;
	}

	public Cursor getOrders() {
		SQLiteDatabase db = getReadableDatabase();
		String sql = "SELECT * FROM " + TABLE_DRINKS + " d, " + TABLE_ORDER + " o"
						+ " WHERE d." + DRINK_ID + " = o." + ORDER_DRINK_ID
						+ " ORDER BY o." + ORDER_ID + " DESC";
		return db.rawQuery(sql, null);
	}
	
	public Cursor getOpenOrders() {
		SQLiteDatabase db = getReadableDatabase();
		String sql = "SELECT o." + ORDER_ID + ", d." + DRINK_GTIN + ", o." + ORDER_AMOUNT + " FROM " + TABLE_DRINKS + " d, " + TABLE_ORDER + " o"
						+ " WHERE d." + DRINK_ID + " = o." + ORDER_DRINK_ID
						+ " AND " + ORDER_IS_BOOKED + " = 0"
						+ " ORDER BY o." + ORDER_ID;
		return db.rawQuery(sql, null);
	}

	public int deleteOrder(long id) {
		SQLiteDatabase db = getReadableDatabase();
		return db.delete(TABLE_ORDER, ORDER_ID + " = ?", new String[] { String.valueOf(id) });
	}

	public int completeOrderStatus(int orderId) {
		SQLiteDatabase db = getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(ORDER_IS_BOOKED, true);
		return db.update(TABLE_ORDER, values, ORDER_ID + " = ?",
				new String[] { String.valueOf(orderId) });
	}

	public int updateSyncTime(int orderId) {
		SQLiteDatabase db = getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(ORDER_LAST_TRY, System.currentTimeMillis());
		return db.update(TABLE_ORDER, values, ORDER_ID + " = ?",
				new String[] { String.valueOf(orderId) });
	}
}
